package dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import modle.UserInformation;
import util.JdbcUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

/**
 * 1、用于对用户个人信息数据库的各种操作
 * 2、只适用于MySQL数据库
 * 
 * @author Lzx
 *
 */
public class UserInformationDaoJdbcImpl implements UserInformationDAO {

	@Override
	public boolean writeUserInformation(UserInformation userInformation) {
		Connection conn = null;
		PreparedStatement pstat = null;
		//表中有account,nickname,sex,score这几列
		String sql = "insert into user_information(account,nickname,sex,score) values(?,?,?,?) ";
		int result = -1;
		
		try {
			conn = JdbcUtil.getConnection();
			pstat = conn.prepareStatement(sql);
			pstat.setString(1,userInformation.getAccount());
			pstat.setString(2,userInformation.getNickname());
			pstat.setInt(3, userInformation.getSex());
			pstat.setInt(4, userInformation.getScore());
			result = pstat.executeUpdate();
		} catch (SQLException sqle) {
			sqle.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.realeaseAll(null,pstat, conn);
		}
		return result>0 ? true : false;
	}

	@Override
	public UserInformation readUserInformation(String account) {
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		//表中有account,nickname,sex,score这几列
		String sql = "select * from user_information where account like ?";
		UserInformation userInformation = null;
		
		try {
			conn = JdbcUtil.getConnection();
			pstat = conn.prepareStatement(sql);
			pstat.setString(1, account);
			rs = pstat.executeQuery();
			while(rs.next()){
				String ac = rs.getString("account");
				String na = rs.getString("nickname");
				int se = rs.getInt("sex");
				int sc = rs.getInt("score");
				userInformation = new UserInformation(ac, na, se, sc);
			}
		}catch (SQLException sqle) {
			sqle.printStackTrace();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			JdbcUtil.realeaseAll(rs,pstat, conn);
		}
		
		return userInformation;
	}

	@SuppressWarnings("resource")
	@Override
	public boolean changeScore(String account , int change) {
		// TODO Auto-generated method stub
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		int sc = 0;
		
		String sql = "select * from user_information where account like ?";

		System.out.println(sql);
		try {
			conn = JdbcUtil.getConnection();
			
			pstat = conn.prepareStatement(sql);
			pstat.setString(1, account);
			rs = pstat.executeQuery();
			while(rs.next()){
				sc = rs.getInt("score");
			}
			sc += change;
			sql = "update user_information set score = " + sc + " where account = " + account;
			pstat = conn.prepareStatement(sql);
			pstat.executeUpdate();
			
		}catch (SQLException sqle) {
			sqle.printStackTrace();
			return false;
		}catch(Exception e){
			e.printStackTrace();
			return false;
		}finally{
			JdbcUtil.realeaseAll(rs,pstat, conn);
		}
		
		return true;
	}

	@Override
	public List<UserInformation> readAllUserInformation() {
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		//表中有account,nickname,sex,score这几列
		String sql = "select * from user_information order by score desc ";
		List<UserInformation> userInformation = new ArrayList<UserInformation>();
		
		try {
			conn = JdbcUtil.getConnection();
			pstat = conn.prepareStatement(sql);
			rs = pstat.executeQuery();
			while(rs.next()){
				String ac = rs.getString("account");
				String na = rs.getString("nickname");
				int se = rs.getInt("sex");
				int sc = rs.getInt("score");
				userInformation.add(new UserInformation(ac, na, se, sc));
			}
		}catch (SQLException sqle) {
			sqle.printStackTrace();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			JdbcUtil.realeaseAll(rs,pstat, conn);
		}
		return userInformation;
	}

}
